﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace GroupProject
{
    public partial class frmCategories : Form
    {
        public frmCategories()
        {
            InitializeComponent();

        }

        #region sql connect
        static SqlConnection con;
        static SqlCommand cmd;
        //static string constr = @"server=.;database=TSQLFundamentals2008;uid=sa;pwd=m1ch@3ly1428";
        //DataTable dtable;
        //DataView dview;
        #endregion

        void InitializeAOObject()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            con.Open();
            cmd = new SqlCommand();
            cmd.Connection = con;


        }

        public bool valid()
        {
            bool valid = true;
            if (txtCategoryName.Text.Length == 0 || txtCategoryName.Text.Trim().Equals(""))
            {
                errorProvider1.Clear();
                errorProvider1.SetError(txtCategoryName, "Category name cannot empty!");
                return false;
            }

            if (txtDescription.Text.Length == 0 || txtDescription.Text.Trim().Equals(""))
            {
                errorProvider1.Clear();
                errorProvider1.SetError(txtDescription, "Description name cannot empty!");
                return false;
            }

            if (valid == false)
            {
                return false;
            }
            return true;
        }

        void loadCombobox()
        {
            List<string> cbSearchData = new List<string>();
            cbSearchData.Add("--Choose search option--");
            cbSearchData.Add("CATEGORY ID");
            cbSearchData.Add("CATEGORY NAME");
            cbSearch.DataSource = cbSearchData;
        }

        void loadData()
        {
            //lbCategory.Parent = pictureBox1;
            //lbCategory.BackColor = Color.Transparent;

            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_GetCategories";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvCategories.Rows.Clear();
                while (dr.Read())
                {
                    dgvCategories.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        bool addCategories()
        {
            bool result;
            try
            {
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_InsertCategories";

                SqlParameter para = new SqlParameter("@categoryname", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtCategoryName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@description", SqlDbType.NVarChar);
                para.Size = 200;
                para.Value = txtDescription.Text;
                cmd.Parameters.Add(para);

                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }

            if (result == false)
            {
                return false;
            }
            return true;
        }

        bool updateCategories()
        {
            bool result;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_UpdateCategories";

                SqlParameter para = new SqlParameter("categoryid", SqlDbType.Int);
                para.Value = txtCategoryID.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@categoryname", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtCategoryName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@description", SqlDbType.NVarChar);
                para.Size = 200;
                para.Value = txtDescription.Text;
                cmd.Parameters.Add(para);

                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                result = false;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        bool deleteCategories()
        {
            bool result = true;
            try
            {
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_DeleteCategories";

                SqlParameter para = new SqlParameter("@categoryid", SqlDbType.Int);
                para.Value = Int32.Parse(txtCategoryID.Text);
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
                result = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        void newCategories()
        {
            errorProvider1.Clear();
            txtCategoryID.Clear();
            txtCategoryName.Clear();
            txtDescription.Clear();
            txtCategoryName.Focus();
        }

        void searchByID()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please enter an key");
                    return;
                }
                InitializeAOObject();

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SearchCategories";

                SqlParameter para = new SqlParameter("categoryid", SqlDbType.Int);
                para.Value = txtSearch.Text;
                cmd.Parameters.Add(para);
                String returnID = cmd.ExecuteScalar().ToString();

                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_GetCategories";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvCategories.Rows.Clear();
                while (dr.Read())
                {
                    if (dr[0].ToString().Equals(returnID))
                    {
                        dgvCategories.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
                    }
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("ID " + txtSearch.Text + " is not exist");
                dgvCategories.Rows.Clear();
            }
            finally
            {
                con.Close();
            }
        }

        void searchByName()
        {
            try
            {
                if (txtSearch.Text.Length == 0)
                {
                    MessageBox.Show("Please enter an key");
                    return;
                }
                InitializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_SearchCategoriesByNames";

                SqlParameter para = new SqlParameter("@categoryname", SqlDbType.NVarChar);
                para.Size = 15;
                para.Value = txtSearch.Text;

                cmd.Parameters.Add(para);
                //String returnName = cmd.ExecuteScalar().ToString();

                //cmd = new SqlCommand();
                //cmd.Connection = con;
                //cmd.CommandType = CommandType.StoredProcedure;
                //cmd.CommandText = "sp_GetCategories";
                SqlDataReader dr = cmd.ExecuteReader();
                dgvCategories.Rows.Clear();
                while (dr.Read())
                {
                    //if (dr[1].ToString().Equals(returnName))
                    //{
                        dgvCategories.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
                    //}
                }
                dr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(txtSearch.Text + " is not Exist, please enter exactly Category name!");
            }
            finally
            {
                con.Close();
            }
        }

        #region event
        private void frmCategories_Load(object sender, EventArgs e)
        {
            InitializeAOObject();
            loadData();
            newCategories();
            loadCombobox();
        }

        private void frmCategories_FormClosing(object sender, FormClosingEventArgs e)
        {
            DialogResult rs;
            rs = MessageBox.Show("Are you sure", "Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (rs == DialogResult.No)
            {
                e.Cancel = true;
            }
        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            newCategories();
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            bool check;
            if (valid())
            {
                try
                {
                    check = updateCategories();
                    if (check == true)
                    {
                        MessageBox.Show("Updated");
                        loadData();
                        newCategories();
                    }
                    else
                    {
                        MessageBox.Show("Update Failed");
                        loadData();
                        newCategories();
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            bool check;
            if (!valid())
            {
                return;
            }

            check = addCategories();
            if (check == true)
            {
                MessageBox.Show("Added");
            }
            else
            {
                MessageBox.Show("Add Failed");
            }
            loadData();
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            bool del;
            if (MessageBox.Show("Are you sure to delele?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                del = deleteCategories();
                if (del == true)
                {
                    loadData();
                    newCategories();
                    MessageBox.Show("Deleted");
                }
                else
                {
                    loadData();
                    newCategories();
                    MessageBox.Show("Delete Failed");
                }
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (cbSearch.Text == "--Choose search option--")
                MessageBox.Show("Please Choose search option");
            else
                if (cbSearch.Text == "CATEGORY ID")
                {
                    searchByID();
                }
                else
                    if (cbSearch.Text == "CATEGORY NAME")
                    {
                        searchByName();
                        //MessageBox.Show("Chua hien thuc tinh nang nay");
                    }
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            InitializeAOObject();
            loadData();
            loadCombobox();
            newCategories();
        }

        private void dgvCategories_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            txtCategoryID.Text = dgvCategories.CurrentRow.Cells[0].Value.ToString();
            txtCategoryName.Text = dgvCategories.CurrentRow.Cells[1].Value.ToString();
            txtDescription.Text = dgvCategories.CurrentRow.Cells[2].Value.ToString();
            dgvCategories.ReadOnly = true;
            dgvCategories.MultiSelect = false;
        }
        #endregion

    }
}
